﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Collections;
using DelonixRegiaHotel.Classes;
using System.Data.SqlClient;
using System.Configuration;



namespace DelonixRegiaHotel.DBManager
{
    class StaffAndDutyDBManager
    {
        // Staff
        //  Create Staff
        public static int CreateStaff(Staff staff)
        {
            int rowadded = 0;
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT into Staff (staffID, firstName, lastName, Nric, homeAddress, postalCode, dob, password, contactNo, email, accessLevel, bankAccountNo, shiftType, workHours, hourlyRate, dutyTypeID, shiftDay)"
                    + "VALUES (@staffID, @firstname, @lastname, @nric, @homeaddress, @postalcode, @dob, @password, @contactno, @email, @accesslevel, @bankAccountNo, @shifttype, @workhours, @hourlyrate, @dutytypeID, @shiftDay)";

                comm.Parameters.AddWithValue("@staffID", staff.StaffID);
                comm.Parameters.AddWithValue("@shiftDay", staff.ShiftDay);
                comm.Parameters.AddWithValue("@firstname", staff.FirstName);
                comm.Parameters.AddWithValue("@lastName", staff.LastName);
                comm.Parameters.AddWithValue("@nric", staff.Nric);
                comm.Parameters.AddWithValue("@homeaddress", staff.HomeAddress);
                comm.Parameters.AddWithValue("@postalcode", staff.PostalCode);
                comm.Parameters.AddWithValue("@dob", staff.Dob);
                comm.Parameters.AddWithValue("@password", staff.Password);
                comm.Parameters.AddWithValue("@contactno", staff.ContactNo);
                comm.Parameters.AddWithValue("@email", staff.Email);
                comm.Parameters.AddWithValue("@accesslevel", staff.AccessLevel);
                comm.Parameters.AddWithValue("@bankaccountno", staff.BankAccountNo);
                comm.Parameters.AddWithValue("@shifttype", staff.ShiftType);
                comm.Parameters.AddWithValue("@workhours", staff.WorkHours);
                comm.Parameters.AddWithValue("@hourlyRate", staff.HourlyRate);
                comm.Parameters.AddWithValue("@dutytypeID", staff.DutyTypeID);

                rowadded = comm.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowadded;
        }

        // Retrieve Staff
        public static ArrayList GetAllStaffInfo()
        {
            ArrayList staffList = new ArrayList();
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM Staff";
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    Staff staff = new Staff();
                    staff.StaffID = (string)dr["staffID"];
                    staff.DutyTypeID = (string)dr["dutyTypeID"];
                    staff.ShiftDay = Convert.ToString(dr["shiftDay"]);
                    staff.FirstName = (string)dr["firstName"];
                    staff.LastName = (string)dr["lastName"];
                    staff.HomeAddress = (string)dr["homeAddress"];
                    staff.PostalCode = (int)dr["postalCode"];
                    staff.Dob = (DateTime)dr["dob"];
                    staff.Password = (string)dr["password"];
                    staff.ContactNo = (int)dr["contactNo"];
                    staff.Email = (string)dr["email"];
                    staff.AccessLevel = (int)dr["accessLevel"];
                    staff.BankAccountNo = (int)dr["bankAccountNo"];
                    staff.ShiftType = (string)dr["shiftType"];
                    staff.WorkHours = (int)dr["workHours"];
                    staff.HourlyRate = Convert.ToDouble(dr["hourlyRate"]);
                    staff.Nric = Convert.ToString(dr["nric"]);
                    staffList.Add(staff);
                }
                dr.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return staffList;

        }

        public static bool GetStaffByNric(Staff nric)
        {
            bool success = false;
            SqlConnection conn = null;

            try
            {
                 conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT nric FROM Staff WHERE nric=@nric";
                comm.Parameters.AddWithValue("@nric", nric.Nric);      
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    success = true;
                }
                dr.Close();
                conn.Close();
                
            }

            catch (SqlException ex)
            {
                throw ex;
            }
            return success;
            
        }


        // Update Staff
        public static int UpdateStaffInfoByID(Staff staffId)
        {
            int rowupdated = 0;
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE Staff SET shiftDay=@shiftDay, homeAddress=@homeAddress, postalCode=@postalCode, contactNo=@contactNo, email=@email, bankAccountNo=@bankAccountNo, password=@password, accessLevel=@accessLevel, dutyTypeID=@dutyTypeID, shiftType=@shiftType, hourlyRate=@hourlyRate WHERE staffID=@staffID";
                comm.Parameters.AddWithValue("@staffID", staffId.StaffID);
                comm.Parameters.AddWithValue("@accessLevel", staffId.AccessLevel);
                comm.Parameters.AddWithValue("@dutyTypeID", staffId.DutyTypeID);
                comm.Parameters.AddWithValue("@shiftDay", staffId.ShiftDay);
                comm.Parameters.AddWithValue("@shiftType", staffId.ShiftType);
                comm.Parameters.AddWithValue("@homeAddress", staffId.HomeAddress);
                comm.Parameters.AddWithValue("@postalCode", staffId.PostalCode);
                comm.Parameters.AddWithValue("@contactNo", staffId.ContactNo);
                comm.Parameters.AddWithValue("@email", staffId.Email);
                comm.Parameters.AddWithValue("@bankAccountNo", staffId.BankAccountNo);
                comm.Parameters.AddWithValue("@password", staffId.Password);  
                comm.Parameters.AddWithValue("@hourlyRate", staffId.HourlyRate);

                rowupdated = comm.ExecuteNonQuery();
                conn.Close();
            }

            catch (SqlException ex)
            {
                throw ex;
            }

            return rowupdated;
        }

        public static int StaffEditOwnInfoByID(Staff staff)
        {
            int rowupdated = 0;
            SqlConnection conn= null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE Staff SET firstName=@firstName, lastName=@lastName, nric=@nric, homeAddress=@homeAddress, postalCode=@postalCode, dob=@dob, password=@password, contactNo=@contactNo, email=@email, bankAccountNo=@bankAccountNo WHERE staffID=@staffID";
                comm.Parameters.AddWithValue("@staffID", staff.StaffID);
                comm.Parameters.AddWithValue("@firstName", staff.FirstName);
                comm.Parameters.AddWithValue("@lastName", staff.LastName);
                comm.Parameters.AddWithValue("@nric", staff.Nric);
                comm.Parameters.AddWithValue("@homeAddress", staff.HomeAddress);
                comm.Parameters.AddWithValue("@postalCode", staff.PostalCode);
                comm.Parameters.AddWithValue("@dob", staff.Dob);
                comm.Parameters.AddWithValue("@password", staff.Password);
                comm.Parameters.AddWithValue("@contactNo", staff.ContactNo);
                comm.Parameters.AddWithValue("@email", staff.Email);
                comm.Parameters.AddWithValue("@bankAccountNo", staff.BankAccountNo);

                rowupdated = comm.ExecuteNonQuery();
                conn.Close();
            }

            catch (SqlException ex)
            {
                throw ex;
            }

           return rowupdated;
        }

        // Delete Staff
        public static int DeleteStaffByID(String staffid)
        {
            DeleteBookingDetailsModRecordIfAny(staffid);
            DeleteCustomerModRecordIfAny(staffid);

            int rowdeleted = 0;
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "DELETE from Staff WHERE staffID=@staffID";
                comm.Parameters.AddWithValue("@staffID", staffid);
                rowdeleted = comm.ExecuteNonQuery();
            }

            catch (SqlException ex)
            {
                throw ex;
            }

            return rowdeleted;
        }

        public static void DeleteBookingDetailsModRecordIfAny(string staffID)
        {
            try
            {
                SqlConnection conn = null;
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * from BookingDetailsModRecord WHERE staffID=@staffID";
                comm.Parameters.AddWithValue("@staffID", staffID);
                SqlDataReader dr = comm.ExecuteReader();

                if (dr.Read())
                {
                    conn.Close();
                    SqlConnection conn2 = null;
                    conn2 = new SqlConnection();
                    conn2.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                    conn2.Open();

                    SqlCommand comm2 = new SqlCommand();
                    comm2.CommandText = "DELETE from BookingDetailsModRecord WHERE staffID=@staffID";
                    comm2.Parameters.AddWithValue("@staffID", staffID);
                    comm2.ExecuteNonQuery();
                    conn2.Close();
                }
                conn.Close();
            }

            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static void DeleteCustomerModRecordIfAny(string staffID)
        {
            try
            {
                SqlConnection conn = null;
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM CustomerModRecord WHERE staffID=@staffID";
                comm.Parameters.AddWithValue("@staffID", staffID);
                SqlDataReader dr = comm.ExecuteReader();

                if (dr.Read())
                {
                    SqlConnection conn1 = null;
                    conn1 = new SqlConnection();
                    conn1.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                    conn1.Open();

                    SqlCommand comm1 = new SqlCommand();
                    comm1.Connection = conn1;
                    comm1.CommandText = "DELETE from CustomerModRecord WHERE staffID=@staffID";
                    comm1.Parameters.AddWithValue("@staffID", staffID);
                    comm1.ExecuteNonQuery();
                    conn1.Close();
                }
            }

            catch (SqlException ex)
            {
                throw ex;
            }
        }


        //Duty
        // Create Duty
        public static int CreateDuty(Duty duty)
        {
            int rowadded = 0;
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT into DutyType (dutyTypeID, dutyName, dutyDescription)"
                + "VALUES (@dutytypeid, @dutyname, @dutydesc)";
                comm.Parameters.AddWithValue("@dutytypeid", duty.DutyTypeID);
                comm.Parameters.AddWithValue("@dutyname", duty.DutyName);
                comm.Parameters.AddWithValue("@dutydesc", duty.DutyDescription);

                rowadded = comm.ExecuteNonQuery();
            }

            catch (SqlException e)
            {
                throw e;
            }

            return rowadded;
        }

        // Retrieve Duty
        public static ArrayList GetAllDutyInfo()
        {
            ArrayList dutyList = new ArrayList();
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM DutyType";
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    Duty duty = new Duty();
                    duty.DutyTypeID = (string)dr["dutyTypeID"];
                    duty.DutyName = (string)dr["dutyName"];
                    duty.DutyDescription = (string)dr["dutyDescription"];
                    dutyList.Add(duty);
                }
                dr.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }

            return dutyList;
        }

        public static bool GetDutyByName(Duty dutyname)
        {
            bool sucess = false;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT dutyName FROM DutyType WHERE dutyName=@dutyName";
                comm.Parameters.AddWithValue("@dutyName", dutyname.DutyName);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    sucess = true;
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return sucess;

        }

        //Update Duty
        public static int UpdateDuty(Duty duty)
        {
            int rowupdated = 0;
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE DutyType SET dutyName=@dutyName, dutyDescription=@dutyDescription WHERE dutyTypeID=@dutyTypeID";
                comm.Parameters.AddWithValue("@dutyTypeID", duty.DutyTypeID);
                comm.Parameters.AddWithValue("@dutyName", duty.DutyName);
                comm.Parameters.AddWithValue("@dutyDescription", duty.DutyDescription);

                rowupdated = comm.ExecuteNonQuery();
                conn.Close();
            }

            catch (SqlException e)
            {
                throw e;
            }

            return rowupdated;
        }

        //Delete Duty
        public static int DeleteDuty(string dutyTypeId)
        {
            UpdateToFreeDutyIfAny(dutyTypeId);
            int rowdeleted = 0;
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "DELETE FROM DutyType WHERE DutyTypeId=@dutyTypeID";
                comm.Parameters.AddWithValue("@dutyTypeID", dutyTypeId);
                rowdeleted = comm.ExecuteNonQuery();
            }

            catch (SqlException e)
            {
                throw e;
            }
            return rowdeleted;
        }

        public static void UpdateToFreeDutyIfAny(string dutyTypeID)
        {
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * from staff where dutyTypeID=@dutyTypeID";
                comm.Parameters.AddWithValue("@dutyTypeID",dutyTypeID);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    conn.Close();
                    SqlConnection conn1 = null;
                    conn1 = new SqlConnection();
                    conn1.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                    conn1.Open();
                    SqlCommand comm1 = new SqlCommand();
                    comm1.Connection = conn1;
                    comm1.CommandText = "Update Staff set dutyTypeID = @dutyTypeID where dutyTypeID = @oldDutyTypeID";
                    comm1.Parameters.AddWithValue("@dutyTypeID","00");
                    comm1.Parameters.AddWithValue("@oldDutyTypeID", dutyTypeID);
                    comm1.ExecuteNonQuery();
                    conn1.Close();
                }
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }

        


    }
}
